<?php
//include all support file
include_once ("../modconfig.php");
include_once ("$ROOT_CLASS_DIR/class_database.php");
include_once ("$ROOT_CLASS_DIR/class_common.php");
include_once ("$ROOT_CLASS_DIR/class_login.php");

require_once('OLEwriter.php');
require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');

global $DBCoins,$DBGeneral,$DBConnection, $RS,$Index, $a, $b;


function HeaderingExcel($filename){
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename=$filename");
    header("Expires:0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
}

list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));



if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

$ktrasl = GetParam("office","");
$namafile = "Trial_Balance_".GetParam("office","")."_".$Month."_".$Year;

//http headers
HeaderingExcel($namafile);

//make a workbook
$workbook=new Workbook("-");

//make first worksheet
$fJudul=& $workbook->add_format();
$fJudul->set_bold();
$fJudul->set_size(14);

$fJudulKolom=& $workbook->add_format();
$fJudulKolom->set_bold();
$fJudulKolom->set_size(8);
$fJudulKolom->set_align('centre');
$fJudulKolom->set_border(1);

$fBesar=& $workbook->add_format();
$fBesar->set_size(20);

$fKutipan=& $workbook->add_format();
$fKutipan->set_align('right');
$fKutipan->set_italic();
$fKutipan->set_size(8);
$fKutipan->set_color('blue');

$fList=& $workbook->add_format();
$fList->set_size(8);

$fIsi=& $workbook->add_format();
$fIsi->set_border(1);

$fIsiTengah=& $workbook->add_format();
$fIsiTengah->set_border(1);
$fIsiTengah->set_align('centre');

$angka=& $workbook->add_format();
$angka->set_num_format('#,##0');
$angka->set_border(1);

$angka2=& $workbook->add_format();
$angka2->set_num_format('#,##0.00');
$angka2->set_border(1);

$angkatengah=& $workbook->add_format();
$angkatengah->set_num_format('#,##0');
$angkatengah->set_border(1);
$angkatengah->set_align('centre');

$tanggal=& $workbook->add_format();
$tanggal->set_num_format('D-MMM-YYYY');
$tanggal->set_border(1);


$worksheet1= & $workbook->add_worksheet("Trial_Balance");
//seting zoom size
//75 is 75% zoom size
$worksheet1->set_zoom(100);

//set portrait page
$worksheet1->set_portrait(100);

//set a4 paper size
$worksheet1->set_paper(9);

//set hide gridlines
$worksheet1->hide_gridlines();

//set print area
$worksheet1->print_area(0,0,15,5);

//set page header
$worksheet1->set_header("header",$margin=2);


//set column
/*
1	2	3	4	5	6	7	8	9	0	11	12
ktrasl	ledger	skada	namacc	tglbkt	nobkt	urut	uraian	noref	jumlah	debet	credit
6	6	10	25	12	15	6	25	15	15	15	15
*/

$worksheet1->set_column(1,1,7);// no
$worksheet1->set_column(2,2,6);// ktrasl
$worksheet1->set_column(3,3,6);// ledger
$worksheet1->set_column(4,4,10); // skada
$worksheet1->set_column(5,5,40); // namacc
$worksheet1->set_column(6,6,12); // tglbkt
$worksheet1->set_column(7,7,15); // nobkt
$worksheet1->set_column(8,8,6); // urut
$worksheet1->set_column(9,9,35); // uraian
$worksheet1->set_column(10,10,15); // noref
$worksheet1->set_column(11,11,15); // jumlah
$worksheet1->set_column(12,12,15); // debet
$worksheet1->set_column(13,13,15);  // credit

// buat koneksi database dan cari data
$DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);



$SQL = " SELECT distinct ktr.ktrnam, am.ktrasl,am.ledger, am.skada,tblcoa.namacc, DATEDIFF(am.tglbkt,str_to_date( '1899-12-30', '%Y-%m-%d' ) ) AS tglbkt,am.nobkt, am.urut, am.uraian, am.noref, am.jumlah, am.rate,  IF(am.dk='D',am.rupiah,0) as debet, IF(am.dk='K',am.rupiah,0) as credit FROM ais_memorial am 
LEFT JOIN tblcoa on am.skada = tblcoa.skada
LEFT JOIN tblkantor ktr on am.ktrasl = ktr.ktrasl
WHERE MONTH(am.tglbkt)=$Month AND YEAR(am.tglbkt)=$Year AND am.ktrasl=$ktrasl 
 and tblcoa.ledger=$Year ORDER BY am.tglbkt, am.skada";

   
$results = $DBConnection->dbc->get_results($SQL, ARRAY_A);

$title='TRIAL BALANCE of '.$Month." - ".$Year;
$perush = 'PT. Masaji Tatanan Container';

//Header
$worksheet1->write_string(1,1,$title,$fJudul);
$worksheet1->write_string(2,1,$perush,$fJudul);
$worksheet1->write_string(3,1,$results[0]["ktrnam"],$fJudul);

$worksheet1->write_string(4,1,"No",$fJudulKolom);
$worksheet1->write_string(4,2,"ktrasl",$fJudulKolom);
$worksheet1->write_string(4,3,"ledger",$fJudulKolom);
$worksheet1->write_string(4,4,"skada",$fJudulKolom);
$worksheet1->write_string(4,5,"namacc",$fJudulKolom);
$worksheet1->write_string(4,6,"tglbkt",$fJudulKolom);
$worksheet1->write_string(4,7,"nobkt",$fJudulKolom);
$worksheet1->write_string(4,8,"urut",$fJudulKolom);
$worksheet1->write_string(4,9,"uraian",$fJudulKolom);
$worksheet1->write_string(4,10,"noref",$fJudulKolom);
$worksheet1->write_string(4,11,"jumlah",$fJudulKolom);
$worksheet1->write_string(4,12,"debet",$fJudulKolom);
$worksheet1->write_string(4,13,"credit",$fJudulKolom);

$i=5;
$debit=0;
$kredit=0;
if ($results) {
 foreach ($results as $row) {
    $a = $i++;
    $worksheet1->write_number($a,1,$i-5,$angka);
    $worksheet1->write_string($a,2,$row["ktrasl"],$fIsi);
    $worksheet1->write_string($a,3,$row["ledger"],$fIsi);
    $worksheet1->write_string($a,4,$row["skada"],$fIsi);
    $worksheet1->write_string($a,5,$row["namacc"],$fIsi);
    $worksheet1->write_number($a,6,$row["tglbkt"],$tanggal);
    $worksheet1->write_string($a,7,$row["nobkt"],$fIsi);
    $worksheet1->write_number($a,8,$row["urut"],$angka);
    $worksheet1->write_string($a,9,$row["uraian"],$fIsi);
    $worksheet1->write_string($a,10,$row["noref"],$fIsi);
    $worksheet1->write_number($a,11,$row["jumlah"],$angka2);
    $worksheet1->write_number($a,12,$row["debet"],$angka2);
    $worksheet1->write_number($a,13,$row["credit"],$angka2);
    $debit=$debit+$row["debet"];
    $kredit=$kredit+$row["credit"];

 }
$worksheet1->write_number($a+1,12,$debit,$angka2);
$worksheet1->write_number($a+1,13,$kredit,$angka2);
$worksheet1->write_string($a+2,12,"Balance ",$fIsi);
$worksheet1->write_number($a+2,13,$debit-$kredit,$angka2);

}
	
$workbook->close();
?>
